<?php
if(!defined('FROM_INDEX'))exit('Invalid request');

require_once ROOT_PATH.'include/sql/class_db.php';
require_once ROOT_PATH.'include/sql/base.sql.php';

class StorySqlFunc extends BaseSqlFunc
{
	/**
	 *
	 * 根据UID获取上次上传时间
	 * @param String $id
	 */
	function get_last_add($uid)
	{
		$sql = <<<SQL
select
	s.create_time
from
	`{$this->pre}story` s
where
	s.`user_id` = ?
order by s.create_time desc
SQL;

		$result = DBFunction::getInstance()->getOne($sql,array($uid));
		
		return ($result != false) ? $result : 0;
	}
	
	/**
	 *
	 * 根据ID获取故事信息
	 * @param String $id
	 */
	function get($uid, $id)
	{
		$sql = <<<SQL
select
	s.*, 
	u.id as "user_id", u.name as "user_name", u.image as "user_image", u.gender as "user_gender",
	ul.user_id <> '' as "is_like"
from
	`{$this->pre}story` s
	inner join `{$this->pre}user` u on s.user_id = u.id
	left outer join `{$this->pre}user_like` ul on ul.user_id=? and ul.story_id = s.id
where
	s.`id` = ?
SQL;

		$result = DBFunction::getInstance()->getRow($sql,array($uid, $id));
		
		return check_array($result) ? $result : array();
	}

	/**
	 *
	 * 获取热门故事列表
	 * @param String $uid
	 */
	function get_new_list($request)
	{
		$uid = $request['uid'];
		$limit = $request['page_size'];
		$num = $request['page_index'];
		
		$time = time()-120;

		$sql = <<<SQL
select
	count(*)
from
	`{$this->pre}story` s
	inner join `{$this->pre}user` u on s.user_id = u.id
	left outer join `{$this->pre}user_like` ul on ul.story_id = s.id and ul.user_id=?
where s.create_time < ?
SQL;
		
		$count = DBFunction::getInstance()->getOne($sql, array($uid, $time));
		if ($count < 1) return array();
		
		$sql = <<<SQL
select
	s.*, 
	u.id as "user_id", u.name as "user_name", u.image as "user_image", u.gender as "user_gender",
	ul.user_id <> '' as "is_like"
from
	`{$this->pre}story` s
	inner join `{$this->pre}user` u on s.user_id = u.id
	left outer join `{$this->pre}user_like` ul on ul.story_id = s.id and ul.user_id=?
where s.create_time < ?
order by s.create_time desc
SQL;

		$list = DBFunction::getInstance()->selectLimit($sql, $limit, $num, array($uid, $time));

		$result['total_count'] = $count;
		$result['list'] = $list;
		
		return $result;
	}
	
	/**
	 *
	 * 获取热门故事列表
	 * @param String $uid
	 */
	function get_hot_list($request)
	{
		$uid = $request['uid'];
		$limit = $request['page_size'];
		$num = $request['page_index'];

		$sql = <<<SQL
select
	count(*)
from
	`{$this->pre}story` s
	inner join `{$this->pre}user` u on s.user_id = u.id
	left outer join `{$this->pre}user_like` ul on ul.story_id = s.id and ul.user_id=?
SQL;
		
		$count = DBFunction::getInstance()->getOne($sql, array($uid));
		if ($count < 1) return array();
		
		$sql = <<<SQL
select
	s.*, 
	u.id as "user_id", u.name as "user_name", u.image as "user_image", u.gender as "user_gender",
	ul.user_id <> '' as "is_like"
from
	`{$this->pre}story` s
	inner join `{$this->pre}user` u on s.user_id = u.id
	left outer join `{$this->pre}user_like` ul on ul.story_id = s.id and ul.user_id=?
order by s.order_num desc, s.like_count+s.comment_count desc
SQL;

		$list = DBFunction::getInstance()->selectLimit($sql, $limit, $num, array($uid));

		$result['total_count'] = $count;
		$result['list'] = $list;
		
		return $result;
	}
	
	/**
	 *
	 * 根据单独用户获取故事列表
	 * @param String $uid
	 */
	function get_list_by_follow($request)
	{
		$uid = $request['uid'];
		$limit = $request['page_size'];
		$num = $request['page_index'];

		$sql = <<<SQL
select
	count(*)
from
	`{$this->pre}story` s
	inner join `{$this->pre}user` u on s.user_id = u.id
	left join `{$this->pre}user_follow` uf on uf.user_id=? and s.user_id = uf.f_user_id
	left outer join `{$this->pre}user_like` ul on ul.user_id=? and ul.story_id = s.id
where
	uf.`user_id` = ?
	or s.user_id = ?
SQL;

		$count = DBFunction::getInstance()->getOne($sql, array($uid, $uid, $uid, $uid));
		if ($count < 1) return array();
		
		$sql = <<<SQL
select
	s.*, 
	u.id as "user_id", u.name as "user_name", u.image as "user_image", u.gender as "user_gender",
	ul.user_id <> '' as "is_like"
from
	`{$this->pre}story` s
	inner join `{$this->pre}user` u on s.user_id = u.id
	left join `{$this->pre}user_follow` uf on uf.user_id=? and s.user_id = uf.f_user_id
	left outer join `{$this->pre}user_like` ul on ul.user_id=? and ul.story_id = s.id
where
	uf.`user_id` = ?
	or s.user_id = ?
order by s.create_time desc
SQL;

		$list = DBFunction::getInstance()->selectLimit($sql, $limit, $num, array($uid, $uid, $uid, $uid));

		$result['total_count'] = $count;
		$result['list'] = $list;
		
		return $result;
	}
	
	/**
	 *
	 * 根据单独用户获取故事列表
	 * @param String $uid
	 */
	function get_list_by_uid($request)
	{
		$uid = $request['uid'];
		$id = $request['id'];
		$limit = $request['page_size'];
		$num = $request['page_index'];
		
		$time = time()-120;

		$sql = <<<SQL
select
	count(*)
from
	`{$this->pre}story` s
	inner join `{$this->pre}user` u on s.user_id = u.id
	left outer join `{$this->pre}user_like` ul on ul.user_id=? and ul.story_id = s.id
where
	s.`user_id` = ?
	and s.create_time < ?
SQL;
		
		$count = DBFunction::getInstance()->getOne($sql, array($uid, $id, $time));
		if ($count < 1) return array();
	
		$sql = <<<SQL
select
	s.*,
	u.id as "user_id", u.name as "user_name", u.image as "user_image", u.gender as "user_gender",
	ul.user_id <> '' as "is_like"
from
	`{$this->pre}story` s
	inner join `{$this->pre}user` u on s.user_id = u.id
	left outer join `{$this->pre}user_like` ul on ul.user_id=? and ul.story_id = s.id
where
	s.`user_id` = ?
	and s.create_time < ?
order by s.id desc
SQL;

		$list = DBFunction::getInstance()->selectLimit($sql, $limit, $num, array($uid, $id, $time));
		
		$result['total_count'] = $count;
		$result['list'] = $list;
		
		return $result;
	}

	/**
	 *
	 * 根据用户列表获取故事列表
	 * @param String $uids
	 */
	function get_list_by_uids($request)
	{
		$uids = $request['uids'];
		$limit = $request['page_size'];
		$num = $request['page_index'];

		$sql = <<<SQL
select
	count(*)
from
	`{$this->pre}story` s
	inner join `{$this->pre}user` u on s.user_id = u.id
where
	s.`uid` in (?)
order by s.id desc
SQL;
		
		$count = DBFunction::getInstance()->getOne($sql, array($uids));
		if ($count < 1) return array();
		
		$sql = <<<SQL
select
	s.*,
	u.id as "user_id", u.name as "user_name", u.image as "user_image", u.gender as "user_gender"
from
	`{$this->pre}story` s
	inner join `{$this->pre}user` u on s.user_id = u.id
where
	s.`uid` in (?)
order by s.id desc
SQL;

		$list = DBFunction::getInstance()->selectLimit($sql, $limit, $num, array($uids));

		$result['total_count'] = $count;
		$result['list'] = $list;
		
		return $result;
	}

	/**
	 *
	 * 根据话题获取故事列表
	 * @param array $request
	 */
	function get_list_by_tag($request)
	{
		$uid = $request['uid'];
		$tag = "%#".$request['tag']."#%";
		$limit = $request['page_size'];
		$num = $request['page_index'];
		
		$time = time()-120;

		$sql = <<<SQL
select
	count(*)
from
	`{$this->pre}story` s
	inner join `{$this->pre}user` u on s.user_id = u.id
where
	s.`content` like ?
	and s.create_time < ?
SQL;
		
		$count = DBFunction::getInstance()->getOne($sql, array($tag, $time));
		if ($count < 1) return array();
		
		$sql = <<<SQL
select
	s.*,
	u.id as "user_id", u.name as "user_name", u.image as "user_image", u.gender as "user_gender",
	ul.user_id <> '' as "is_like"
from
	`{$this->pre}story` s
	inner join `{$this->pre}user` u on s.user_id = u.id
	left outer join `{$this->pre}user_like` ul on ul.user_id=? and ul.story_id = s.id
where
	s.`content` like ?
	and s.create_time < ?
order by s.id desc
SQL;

		$list = DBFunction::getInstance()->selectLimit($sql, $limit, $num, array($uid, $tag, $time));

		$result['total_count'] = $count;
		$result['list'] = $list;
		
		return $result;
	}

	/**
	 *
	 * 新增故事
	 * @param array $request
	 */
	function add($request)
	{
		$time = time();

		$params = array(
			$request['uid'],
			$request['title'],
			$request['content'],
			$request['location'],
			$request['music_name'],
			$request['music_author'],
			$request['image1'],
			$request['image2'],
			$request['image3'],
			$request['url'],
			0,
			0,
			$time
		);

		$sql = <<<SQL
insert into `{$this->pre}story`
	(`user_id`, `title`, `content`, `location`, `music_name`, 
	`music_author`, `image1`, `image2`, `image3`, 
	`url`, `like_count`, `comment_count`, `create_time`)
values
	(?,?,?,?,?,
	?,?,?,?,
	?,?,?,?)
SQL;

		$result = DBFunction::getInstance()->insert($sql, $params);

		return ($result !== false) ? true : false;
	}

	/**
	 *
	 * 更新故事信息
	 * @param array $request
	 */
	function update($request)
	{
		$params = array(
			$request['title'],
			$request['content'],
			$request['music_name'],
			$request['music_author'],
			$request['image1'],
			$request['image2'],
			$request['image3'],
			$request['url'],
			$request['id'],
		);

		$sql = <<<SQL
update `{$this->pre}story`
set 
	`title`=?,
	`content`=?,
	`music_name`=?,
	`music_author`=?,
	`image1`=?,
	`image2`=?,
	`image3`=?,
	`url`=?
where
	id=?
SQL;

		$result = DBFunction::getInstance()->update($sql, $params);

		return ($result !== false) ? true : false;
	}

	function update_like_count($sid)
	{
		$sql = <<<SQL
update `{$this->pre}story`
set 
	`like_count`=(select count(*) from `{$this->pre}user_like` where `story_id`=?)
where
	id=?
SQL;

		$result = DBFunction::getInstance()->update($sql, array($sid, $sid));

		return ($result !== false) ? true : false;
	}
	
	function update_comment_count($sid)
	{
		$sql = <<<SQL
update `{$this->pre}story`
set 
	`comment_count`=(select count(*) from `{$this->pre}user_comment` where `story_id`=?)
where
	id=?
SQL;

		$result = DBFunction::getInstance()->update($sql, array($sid, $sid));

		return ($result !== false) ? true : false;
	}
	
	/**
	 *
	 * 删除故事
	 * @param String $ids
	 */
	function delete($uid, $sid)
	{
		$sql = <<<SQL
delete from `{$this->pre}story`
where
	id=?
	and user_id=?
SQL;

		$result = DBFunction::getInstance()->execute($sql, array($sid, $uid));

		return ($result !== false) ? true : false;
	}

	/**
	 *
	 * 搜索故事
	 * @param array $request
	 */
	function search($request)
	{
		$title = '%'.$request['title'].'%';
		$limit = $request['page_size'];
		$num = $request['page_index'];

		$sql = <<<SQL
select
	count(*)
from
	`{$this->pre}story` s
where
	s.`title` like ?
	or s.`content` like ?
order by s.id desc
SQL;
		
		$count = DBFunction::getInstance()->selectLimit($sql, array($title));
		if ($count < 1) return array();
		
		$sql = <<<SQL
select
	s.*
from
	`{$this->pre}story` s
where
	s.`title` like ?
	or s.`content` like ?
order by s.id desc
SQL;

		$list = DBFunction::getInstance()->selectLimit($sql,$limit, $num, array($title));

		$result['total_count'] = $count;
		$result['list'] = $list;
		
		return $result;
	}

	/**
	 *
	 * 新增故事like
	 * @param array $request
	 */
	function add_like($request)
	{
		$time = time();

		$params = array(
		$request['uid'],
		$request['sid'],
		$time
		);

		$sql = <<<SQL
insert into `{$this->pre}user_like`
	(`user_id`, `story_id`, `create_time`)
values
	(?,?,?)
SQL;

		$result = DBFunction::getInstance()->insert($sql, $params);

		return ($result !== false) ? true : false;
	}

	function check_like($request)
	{
		$sql = <<<SQL
select
	count(*)
from
	`{$this->pre}user_like` s
where
	s.`user_id` = ?
	and s.story_id=?
SQL;

		$result = DBFunction::getInstance()->getOne($sql, array($request['uid'],$request['sid'],));

		return ($result > 0) ? true : false;
	}
	
	/**
	 *
	 * 获取故事like列表
	 * @param String $sid
	 */
	function get_like_list($request)
	{
		$id = $request['id'];
		$limit = $request['page_size'];
		$num = $request['page_index'];

		$sql = <<<SQL
select
	u.id, u.name, u.image, u.gender
from
	`{$this->pre}user_like` s
	inner join `{$this->pre}user` u on s.user_id = u.id
where
	s.`story_id` = ?
order by s.create_time desc
SQL;

		$result = DBFunction::getInstance()->selectLimit($sql,$limit, $num, array($id));

		return check_array($result) ? $result : array();
	}

	/**
	 *
	 * 新增故事评论
	 * @param array $request
	 */
	function add_comment($request)
	{
		$time = time();

		$params = array(
		$request['uid'],
		$request['sid'],
		$request['comment'],
		$time
		);

		$sql = <<<SQL
insert into `{$this->pre}user_comment`
	(`user_id`, `story_id`, `comment`, `create_time`)
values
	(?,?,?,?)
SQL;

		$result = DBFunction::getInstance()->insert($sql, $params);

		return ($result !== false) ? true : false;
	}

	function check_comment($request)
	{
		$sql = <<<SQL
select
	count(*)
from
	`{$this->pre}user_comment` s
where
	s.`user_id` = ?
	and s.story_id=?
SQL;

		$result = DBFunction::getInstance()->getOne($sql, array($request['uid'], $request['sid']));

		return ($result > 0) ? true : false;
	}
	
	/**
	 *
	 * 获取故事评论列表
	 * @param array $request
	 */
	function get_comment_list($request)
	{
		$sid = $request['sid'];
		$limit = $request['page_size'];
		$num = $request['page_index'];

		$sql = <<<SQL
select
	count(*)
from
	`{$this->pre}user_comment` s
	inner join `{$this->pre}user` u on s.user_id = u.id
where
	s.`story_id` = ?
order by s.create_time desc
SQL;

		$count = DBFunction::getInstance()->getOne($sql, array($sid));
		if ($count < 1) return array();
		
		$sql = <<<SQL
select
	s.*,
	u.name as "user_name", u.image as "user_image", u.gender as "user_gender"
from
	`{$this->pre}user_comment` s
	inner join `{$this->pre}user` u on s.user_id = u.id
where
	s.`story_id` = ?
order by s.create_time desc
SQL;

		$list = DBFunction::getInstance()->selectLimit($sql, $limit, $num, array($sid));

		$result['total_count'] = $count;
		$result['list'] = $list;
		
		return $result;
	}

	/**
	 *
	 * 清除热门故事列表顺序
	 * @param String $uid
	 */
	function clear_order($request=array())
	{
		$sql = <<<SQL
update
	`{$this->pre}story`
set 
	order_num=0
SQL;

		$result = DBFunction::getInstance()->execute($sql);

		return ($result !== false) ? true : false;
	}
	
	/**
	 *
	 * 设置热门故事列表顺序
	 * @param String $uid
	 */
	function set_order($request)
	{
		$id = $request['id'];
		$order_num = $request['order_num'];

		$sql = <<<SQL
update
	`{$this->pre}story`
set 
	order_num=?
where
	id=?
SQL;

		$result = DBFunction::getInstance()->execute($sql, array($order_num, $id));

		return ($result !== false) ? true : false;
	}
}